package com.hcc.flow.server.dao.workbench;

import java.util.Date;
import java.util.List;
import java.util.Set;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectKey;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.mapping.StatementType;

import com.hcc.flow.server.common.constant.Constant;
import com.hcc.flow.server.common.vo.CarouselMsgVO;
import com.hcc.flow.server.common.vo.SelectVO;
import com.hcc.flow.server.model.workbench.MsgNoticeRead;
import com.hcc.flow.server.vo.workbench.MsgNoticeReadToUserVO;
import com.hcc.flow.server.vo.workbench.MsgNoticeReadVO;
import com.hcc.flow.server.where.workbench.MsgNoticeReadWhere;

/**
 * MsgNoticeReadDao
 * @author 韩长志 20190604
 *
 */
@Mapper
public interface MsgNoticeReadDao {

    @Select("select * from msg_notice_read t where status='"+Constant.DATA_STATUS_TYPE_NORMAL+"' order by create_time desc")
	List<MsgNoticeRead> listAll();

    @Select("select * from msg_notice_read t where t.read_id = #{id}")
    MsgNoticeRead getById(String id);
    
    @Select("select * from msg_notice_read t where t.notice_id = #{noticeId} and t.flow_verify_status in('3','4') limit 1")
    MsgNoticeRead getByNoticeIdAndVerify(String noticeId);
    
    @Select("select * from msg_notice_read t where t.notice_id = #{noticeId} and t.flow_verify_status = #{flowVerifyStatus}")
    List<MsgNoticeRead> getByNoticeIdAndVerifyStatus(@Param("noticeId") String noticeId,@Param("flowVerifyStatus") String flowVerifyStatus);
    
    @Select("select t.*,CONCAT(u.USER_NAME,'(',o.org_name,')') read_user_name from msg_notice_read t "
    		+"LEFT JOIN sys_user u on t.read_user_id = u.user_id " 
    		+"LEFT JOIN org o on u.ORG_ID = o.org_id "
    		+"where t.notice_id = #{noticeId}")
    List<MsgNoticeReadToUserVO> getByNoticeId(@Param("noticeId") String noticeId);
    
    @Select("select * from msg_notice_read where notice_id = #{noticeId} and status='0'")
    List<MsgNoticeRead> getByNoticeIdVerifyStatus(@Param("noticeId") String noticeId);
    
    @Select("select * from msg_notice_read t where t.notice_id = #{noticeId} and t.read_user_id = #{readUserId} limit 1")
    MsgNoticeRead getByNoticeIdAndReadUserId(@Param("noticeId") String noticeId,@Param("readUserId") String readUserId);
    
    @Select("select * from msg_notice_read t where t.notice_id = #{noticeId} ORDER BY read_time desc LIMIT 1")
    MsgNoticeRead getNewVerifyByNoticeId(String noticeId);

    @Delete("delete from msg_notice_read where read_id = #{id}")
    int delete(String id);
    
    @Update("update msg_notice_read set read_time=now(),status='1' where read_id = #{id} and status='0'")
    int updateRead(@Param("id") String id);
    
    @Update("update msg_notice_read set read_time=#{readTime},status='1',flow_verify_status = #{flowVerifyStatus},flow_verify_reason = #{flowVerifyReason} where read_id = #{readId} and status='0'")
    int updateReadAndVerifyStatus(@Param("readId") String readId,@Param("flowVerifyStatus") String flowVerifyStatus,@Param("flowVerifyReason") String flowVerifyReason,@Param("readTime") Date readTime);
    
    @Update("update msg_notice_read set status='1',flow_verify_status = #{flowVerifyStatus},flow_verify_reason = #{flowVerifyReason} where notice_id = #{noticeId} and status='0'")
    int updateByNoticeIdVerifyStatus(@Param("noticeId") String noticeId,@Param("flowVerifyStatus") String flowVerifyStatus,@Param("flowVerifyReason") String flowVerifyReason);
    
    @Select("select count(t.read_id) from msg_notice_read t LEFT JOIN msg_notice n on t.notice_id=n.notice_id where t.read_user_id = #{readUserId} and t.status='0' and n.type=#{type} and n.`status`='C'")
    int getCount(@Param("readUserId") String readUserId,@Param("type") String type);
    
    @Select("select count(t.read_id) from msg_notice_read t LEFT JOIN msg_notice n on t.notice_id=n.notice_id where t.read_user_id = #{readUserId} and t.status='0' and n.type=#{type} and n.task_type=#{taskType} and n.`status`='C'")
    int getCountByTaskType(@Param("readUserId") String readUserId,@Param("type") String type,@Param("taskType") String taskType);
    
    @Select("select t.read_id readId,CONCAT(n.title,':',REPLACE(REPLACE(n.content,'<br/>',''),'&nbsp;','')) content "
    		+ "from msg_notice_read t "
    		+ "LEFT JOIN msg_notice n on t.notice_id=n.notice_id "
    		+ "where t.read_user_id = #{readUserId} and t.status='0' and n.`status`='C' and n.show_begin_time <= now() and n.show_end_time >= now()")
    List<CarouselMsgVO> getCarouselMsg(String readUserId);

    //@Options(useGeneratedKeys = true, keyProperty = "id")
    @SelectKey(statement="select replace(UUID(),'-','')", keyProperty="readId", before=true, statementType=StatementType.STATEMENT,resultType=String.class)
    @Insert("insert into msg_notice_read(read_id, notice_id, read_user_id, read_time, status,flow_verify_status,flow_verify_reason) values(#{readId}, #{noticeId}, #{readUserId}, #{readTime}, '"+Constant.MSG_READ_TYPE_UNREAD+"',#{flowVerifyStatus},#{flowVerifyReason})")
    int save(MsgNoticeRead msgNoticeRead);
    
    List<MsgNoticeReadVO> MsgNoticeReadList(MsgNoticeReadWhere params);
    
    /**
     * 有效状态
     */
    @Select("select `status` from msg_notice_read where status!='"+Constant.DATA_STATUS_DELETE+"' GROUP BY `status`")
	List<String> listValidStatus();
	
	@Update("<script>"
    		+ "update msg_notice_read set status=#{status} where read_id in "
    		+ "<foreach item='item' index='index' collection='ids' open='(' separator=',' close=')'>"
            	+ "#{item}"
            + "</foreach>"
    		+ "</script>")
    int updateStatus(@Param("ids") List<String> ids,@Param("status") String status);
	
	@Select("select r.read_user_id id,n.task_type name from msg_notice_read r "
			+ "LEFT JOIN msg_notice n on r.notice_id = n.notice_id "
			+ "where n.flow_from_id = #{flowFromId} and n.type = 1 and r.flow_verify_status='2'")
	Set<SelectVO> getReadByFromId(@Param("flowFromId") String flowFromId);
	
	@Update("update msg_notice_read r "
			+ "LEFT JOIN msg_notice n on r.notice_id = n.notice_id "
			+ "set r.read_time=#{readTime},r.status='1',r.flow_verify_status = #{flowVerifyStatus},r.flow_verify_reason = #{flowVerifyReason} "
			+ "where n.flow_from_id = #{flowFromId} and n.type = 1 and r.flow_verify_status='2'")
    int updateReadAndVerifyStatusByFromId(@Param("flowFromId") String flowFromId,@Param("flowVerifyStatus") String flowVerifyStatus,@Param("flowVerifyReason") String flowVerifyReason,@Param("readTime") Date readTime);
	
	@Update("update msg_notice_read r "
			+ "LEFT JOIN msg_notice n on r.notice_id = n.notice_id "
			+ "set r.status='2',r.flow_verify_reason = '其他业务变更将其任务消息屏蔽' "
			+ "where n.flow_from_id = #{flowFromId} and n.type = 1 and r.flow_verify_status='2'")
    int delReadStatusByFromId(@Param("flowFromId") String flowFromId);
}
